package com.sds.shop.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import com.sds.fw.dao.AbDao;
import com.sds.fw.util.DBUtil;
import com.sds.shop.vo.BoardVO;
import com.sds.shop.vo.CategoryVO;

public class BoardDAO extends AbDao {
	private static BoardDAO dao = new BoardDAO();
	public static BoardDAO getInstance(){
		if(dao == null) dao = new BoardDAO();
		return dao;
	}	

	public ArrayList<BoardVO> getBoardList(){
		ArrayList<BoardVO> boardList = new ArrayList<BoardVO>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select * from tb_board where del_flg=? order by reg_dtm desc";
			
			conn = connect();
			ps = conn.prepareStatement(sql);
			ps.setBoolean(1, false);
			rs = ps.executeQuery();
						
			while(rs.next()){
				boardList.add(new BoardVO(rs.getString(1), rs.getString(2), rs.getString(3),
									rs.getString(4), rs.getInt(5), rs.getBoolean(6), 
									rs.getString(7), rs.getString(8)));
			}
			
			close(conn, ps, rs);
		}catch(Exception ex){
			System.out.println("getBoard exception : " + ex);
			close(conn, ps, rs);
		}
		
		return boardList;
	}	
	
	public ArrayList<BoardVO> getBoardListWithReply(){
		ArrayList<BoardVO> boardList = new ArrayList<BoardVO>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select * from tb_board b, " +
						 "(select count(*) reply_count, board_no from tb_reply where del_flg=? group by board_no) r " +
						 "where b.board_no = r.board_no(+) and b.del_flg=? order by to_number(b.board_no) desc";
			
			conn = connect();
			ps = conn.prepareStatement(sql);
			ps.setBoolean(1, false);
			ps.setBoolean(2, false);
			rs = ps.executeQuery();
						
			while(rs.next()){
				boardList.add(new BoardVO(rs.getString(1), rs.getString(2), rs.getString(3),
									rs.getString(4), rs.getInt(5), rs.getBoolean(6), 
									rs.getString(7), rs.getString(8), rs.getInt(9)));
			}
			
			close(conn, ps, rs);
		}catch(Exception ex){
			System.out.println("getBoard exception : " + ex);
			close(conn, ps, rs);
		}
		
		return boardList;
	}	
	
	public BoardVO getBoardDetail(int board_no){
		BoardVO board = new BoardVO();
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try{
			String sql = "select * from tb_board where board_no=?";
			
			conn = connect();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, board_no);
			rs = ps.executeQuery();
						
			rs.next();
			board.setBoard_no(rs.getString(1));
			board.setMem_id(rs.getString(2));
			board.setTitle(rs.getString(3));
			board.setText(rs.getString(4));
			board.setCount(rs.getInt(5));
			board.setDel_flg(rs.getBoolean(6));
			board.setReg_dtm(rs.getString(7));
			board.setMod_dtm(rs.getString(8));
			
			close(conn, ps, rs);
		}catch(Exception ex){
			System.out.println("getBoard exception : " + ex);
			close(conn, ps, rs);
		}
		
		return board;
	}	
	
	public void modifyBoard(String board_no, String title, String text){
		Connection conn = null;
		PreparedStatement ps = null;
		
		try{
			String sql = "update tb_board set title=?, text=?, mod_dtm=sysdate where board_no=?";
			
			conn = connect();
			ps = conn.prepareStatement(sql);
			ps.setString(1, title);
			ps.setString(2, text);
			ps.setString(3, board_no);
			ps.executeUpdate();		
			
			close(conn, ps);
		}
		catch(Exception ex){
			System.out.println("getBoard exception : " + ex);
			close(conn, ps);
		}
	}
	
	public void deleteBoard(String board_no){
		Connection conn = null;
		PreparedStatement ps = null;
		
		try{
			String sql = "update tb_board set del_flg=? where board_no=?";
			
			conn = connect();
			ps = conn.prepareStatement(sql);
			ps.setBoolean(1, true);
			ps.setString(2, board_no);
			ps.executeUpdate();	
			
			close(conn, ps);
		}
		catch(Exception ex){
			System.out.println("getBoard exception : " + ex);
			close(conn, ps);
		}
	}
	
	public void increaseCountBoard(String board_no){
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try{
			String sql = "select count from tb_board where board_no=?";
			conn = connect();
			ps = conn.prepareStatement(sql);
			ps.setString(1, board_no);
			rs = ps.executeQuery();
			rs.next();
			
			sql = "update tb_board set count=? where board_no=?";
						
			ps = conn.prepareStatement(sql);
			ps.setString(1, (Integer.parseInt(rs.getString(1)) + 1)+"");
			ps.setString(2, board_no);
			ps.executeUpdate();		
			
			close(conn, ps);
		}
		catch(Exception ex){
			System.out.println("getBoard exception : " + ex);
			close(conn, ps);
		}
	}
}
